﻿using System;
using System.Linq;
using System.Threading;
using Excel = Microsoft.Office.Interop.Excel;

namespace DataImport
{
    class Program
    {
        private static Excel.Workbook MyBook = null;
        private static Excel.Application MyApp = null;
        private static Excel.Worksheet MySheet = null;

        private const string DB_PATH =
            @"d:\Hien\Projects\Hoang Ngoc\trunk\QuanLyHopDong\Requirements\1. Tien_Do_Thi_Cong_22-02-2015_030015 Quan trong An.xls";

        static void Main(string[] args)
        {
            /* import
            MyApp = new Excel.Application { Visible = false };
            MyBook = MyApp.Workbooks.Open(DB_PATH);
            MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
            var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            using (var db = new HopDongEntities())
            {
                for (var index = 3; index <= lastRow; index++)
                {
                    var values = (Array)MySheet.Range["A" + index, "L" + index].Cells.Value;

                    var soHopDong   = values.GetValue(1, 1) == null ? "" : values.GetValue(1, 1).ToString();
                    var tenHopDong  = values.GetValue(1, 2) == null ? "" : values.GetValue(1, 2).ToString();
                    var tenDonvi    = values.GetValue(1, 3) == null ? "" : values.GetValue(1, 3).ToString();
                    var loaiDuan    = values.GetValue(1, 4) == null ? "" : values.GetValue(1, 4).ToString();
                    var maTuyen     = values.GetValue(1, 5) == null ? "" : values.GetValue(1, 5).ToString();
                    var tinhTrangThiCong = values.GetValue(1, 6) == null ? "" : values.GetValue(1, 6).ToString();
                    var ghiChu      = values.GetValue(1, 7) == null ? "" : values.GetValue(1, 7).ToString();
                    var nhanVatTuA  = values.GetValue(1, 8) == null ? "" : values.GetValue(1, 8).ToString();
                    var tenDiemDau  = values.GetValue(1, 9) == null ? "" : values.GetValue(1, 9).ToString();
                    var tenDiemCuoi = values.GetValue(1, 10) == null ? "" : values.GetValue(1, 10).ToString();
                    var chieuDaiTuyen = values.GetValue(1, 11) == null ? "" : values.GetValue(1, 11).ToString();
                    var tenLoaiCap  = values.GetValue(1, 12) == null ? "" : values.GetValue(1, 12).ToString();

                    Console.WriteLine("{0}\t{1}\t\t{2}", index, soHopDong, maTuyen);

                    // diemdau, diem cuoi
                    var diemDau = db.DiaDiems.FirstOrDefault(x => x.Ma == tenDiemDau);
                    if (diemDau == null)
                    {
                        diemDau = new DiaDiem
                        {
                            Ma = tenDiemDau
                        };
                        db.DiaDiems.Add(diemDau);
                        db.SaveChanges();
                    }

                    var diemCuoi = db.DiaDiems.FirstOrDefault(x => x.Ma == tenDiemCuoi);
                    if (diemCuoi == null)
                    {
                        diemCuoi = new DiaDiem
                        {
                            Ma = tenDiemCuoi
                        };
                        db.DiaDiems.Add(diemCuoi);
                        db.SaveChanges();
                    }

                    // loaiCap
                    var loaiCap = db.LoaiCaps.FirstOrDefault(x => x.Name == tenLoaiCap);
                    if (loaiCap == null)
                    {
                        loaiCap = new LoaiCap
                        {
                            Name = tenLoaiCap
                        };
                        db.LoaiCaps.Add(loaiCap);
                        db.SaveChanges();
                    }

                    // donvi
                    var donVi = db.DonVis.FirstOrDefault(x => x.Ten == tenDonvi);
                    if (donVi == null)
                    {
                        donVi = new DonVi
                        {
                            Ten = tenDonvi
                        };
                        db.DonVis.Add(donVi);
                        db.SaveChanges();
                    }

                    // hopdong
                    var hopDong = db.HopDongs.FirstOrDefault(x => x.SoHopDong == soHopDong);
                    if (hopDong == null)
                    {
                        hopDong = new HopDong
                        {
                            DonViId = donVi.Id,
                            SoHopDong = soHopDong,
                            TenHopDong = tenHopDong,
                            LoaiDuAn = loaiDuan,
                            CreatedBy = 1,
                            CreatedDate = DateTime.Now,
                            ModifiedBy = 1,
                            ModifiedDate = DateTime.Now,
                            EmployeeId = 1,
                            ThiCongXong = true
                        };
                        db.HopDongs.Add(hopDong);
                        db.SaveChanges();
                    }

                    // khoiluong
                    byte tienDoThiCong;
                    switch (tinhTrangThiCong)
                    {
                        case "Đang thi công":
                            tienDoThiCong = 2;
                            break;
                        case "Thi công xong":
                            tienDoThiCong = 3;
                            break;
                        default:
                            tienDoThiCong = 1;
                            break;
                    }

                    var khoiLuong = new KhoiLuongHopDong
                    {
                        HopDongId = hopDong.Id,
                        MaTuyen = maTuyen,
                        GhiChu = ghiChu,
                        TienDoThiCong = tienDoThiCong,
                        DiemDauId = diemDau.Id,
                        DiemCuoiId = diemCuoi.Id,
                        LoaiCapId = loaiCap.Id
                    };

                    if (!string.IsNullOrWhiteSpace(chieuDaiTuyen))
                    {
                        double dChieuDaiTuyen;
                        if (double.TryParse(chieuDaiTuyen, out dChieuDaiTuyen))
                        {
                            khoiLuong.ChieuDaiTuyen = dChieuDaiTuyen;
                        }
                    }

                    byte bNhanVatTuA;
                    switch (nhanVatTuA)
                    {
                        case "Chưa nhận vật tư":
                            bNhanVatTuA = 1;
                            break;
                        case "Đã nhận vật tư":
                            bNhanVatTuA = 2;
                            break;
                        default:
                            bNhanVatTuA = 1;
                            break;
                    }
                    khoiLuong.NhanVatTuA = bNhanVatTuA;

                    db.KhoiLuongHopDongs.Add(khoiLuong);
                    db.SaveChanges();

                    //break;
                    Thread.Sleep(50);
                }
            }

            MyBook.Close();
            */

            // update hopdong
            using (var db = new HopDongEntities())
            {
                var hopDongs = db.HopDongs.ToList();
                foreach (var hopDong in hopDongs)
                {
                    var khoiLuong =
                        db.KhoiLuongHopDongs.FirstOrDefault(x => x.HopDongId == hopDong.Id && x.TienDoThiCong != 3);
                    if (khoiLuong != null)
                    {
                        hopDong.ThiCongXong = false;
                        db.SaveChanges();
                    }
                }
            }

            Console.WriteLine();
        }
    }
}
